package cn.zhaoyan.daoimpl;

import cn.zhaoyan.bean.Customer;
import cn.zhaoyan.dao.Customerdao;
import cn.zhaoyan.util.Jdbcutils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class Customerdaoimpl implements Customerdao{
    /**
     * 查询所有信息
     */
    private JdbcTemplate jdbcTemplate =new JdbcTemplate(Jdbcutils.getDataSource());
    @Override
    public List<Customer> customerdao() {
        String sql="SELECT*FROM customer order by id desc";
        return this.jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Customer.class));

    }

    /**
     * 删除，指定删除
     * @param id
     */
    @Override
    public void daoQuery(String id) {

        String sql = "DELETE FROM customer WHERE id = ?";
        this.jdbcTemplate.update(sql,id);
        }

    /**
     * 批量删除
     * @param select
     */
    @Override
    public void batchDelete(String[] select) {
        for(int i=0;i<select.length;i++){
            String sql = "DELETE FROM customer WHERE id = ?";
            this.jdbcTemplate.update(sql,select[i]);
        }

    }

    @Override
    public Customer selectdao(Customer customer) {
        try {
            String sql="SELECT*FROM customer WHERE phone=?";
            return this.jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Customer.class), customer.getPhone());

        } catch (DataAccessException e) {
            return null;
        }
    }
    @Override
    public void insertdao(Customer customer) {
            String sql = "INSERT INTO `yan`.`customer`(`id`, `phone`, `name`, `age`, `sex`) VALUES (?,?,?,?,?);\n";
            this.jdbcTemplate.update(sql, customer.getId(),customer.getPhone(), customer.getName(), customer.getAge(), customer.getSex());


    }

    @Override
    public Customer updatedao(String id) {
        try {
            String sql = "SELECT*FROM customer WHERE id=?";
            return this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Customer.class),id);
        } catch (DataAccessException e) {
            return null;
        }


    }

    @Override
    public void updatedao1(Customer customer) {
        String sql = "UPDATE `yan`.`customer` SET `phone` = ?, `name` = ?, `age` = ?, `sex` =? WHERE `id` = ?;\n";
        this.jdbcTemplate.update(sql, customer.getPhone(), customer.getName(), customer.getAge(), customer.getSex(),customer.getId());
    }

    /**
     * 查询总条数
     * @return
     * @param parameterMap
     */
    @Override
    public int getCustomerCount(Map<String, String[]> parameterMap) {
       /* try {
            String sql ="select count(1) from customer";
            return  this.jdbcTemplate.queryForObject(sql,Integer.class);
        } catch (DataAccessException e) {
           throw new RuntimeException("查询总条数失败 ");
        }*/
        String sql ="select count(1) from customer where 2=2 ";
        StringBuffer stringBuffer = new StringBuffer(sql);
        //接受可变参数的集合
        ArrayList<Object> objects = new ArrayList<>();
        Set<String> strings = parameterMap.keySet();
        for (String k:strings){
            //排除条件查询以外的key
            if ("name".equals(k) ||"role".equals(k)|| "password".equals(k) || "yanzhengma".equals(k) || "pageNum".equals(k) ||"password1".equals(k)){
                continue;
            }
            String value = parameterMap.get(k)[0];
          //判断条件查询的内容并进行拼接
            if(value!=null && !value.equals("")){
                stringBuffer.append(" and " +k+ " like ? ");//select count(1) from customer where 2=2 and name like '%zhaoyan%' and address like '%上海%'
               objects.add("%" + value + "%");
            }

        }
        return this.jdbcTemplate.queryForObject(stringBuffer.toString(),Integer.class,objects.toArray());
    }

    /**
     * 查询分页显示的数据
     * @param startIndex
     * @param pageSize
     * @param parameterMap
     * @return
     */
    @Override
    public List<Customer> getCustomerLiset(int startIndex, int pageSize, Map<String, String[]> parameterMap) {
      /*  try {
            String sql ="select *from customer  limit ?,?";
            return this.jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Customer.class),startIndex,pageSize);
        } catch (DataAccessException e) {
            throw new RuntimeException("查询总数据失败 ");
        }*/
        String sql ="select * from customer where 2=2 ";
        StringBuffer stringBuffer = new StringBuffer(sql);
        //接受可变参数的集合
        ArrayList<Object> objects = new ArrayList<>();
        Set<String> strings = parameterMap.keySet();
        for (String k:strings){
            //排除条件查询以外的key
            if ("name".equals(k) ||"role".equals(k)|| "password".equals(k) || "yanzhengma".equals(k) || "pageNum".equals(k)||"password1".equals(k)){
                continue;
            }
            String value = parameterMap.get(k)[0];
            //判断条件查询的内容并进行拼接
            if(value!=null && value!=""){
                stringBuffer.append(" and " +k+ " like ? ");//select count(1) from customer where 2=2 and name like '%zhaoyan%' and address like '%上海%'
                objects.add("%"+value+"%");
            }

        }
        stringBuffer.append(" limit ?, ? ");//select count(1) from customer where 2=2 and name like '%zhaoyan%' and address like '%上海%'
        objects.add(startIndex);
        objects.add(pageSize);

        return this.jdbcTemplate.query(stringBuffer.toString(),new BeanPropertyRowMapper<>(Customer.class),objects.toArray());
    }

}

